﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;
using System.Windows.Forms;
using System.Text.RegularExpressions;

public class ExcelColumn
{
    public string FieldName { get; set; }
    public string HeaderText { get; set; }
    public string DataFormat { get; set; }
    public float Width { get; set; }
}

public sealed class ExcelHelper
{
    public static string Excel_Export(DataView data, List<ExcelColumn> columns)
    {
        string fileName = FormGlobals.App_TempFile("xls");
        HSSFWorkbook workbook = new HSSFWorkbook();
        ISheet worksheet = workbook.CreateSheet("Sheet1");
        Excel_PageSetup(worksheet);
        //
        Dictionary<string, ICellStyle> lstStyle = new Dictionary<string, ICellStyle>();
        IRow rowE = worksheet.CreateRow(0);
        //Define CellStyle for header
        ICellStyle cellStyleHeader = workbook.CreateCellStyle();
        IFont f = workbook.CreateFont();
        f.Boldweight = 700;
        cellStyleHeader.SetFont(f);
        //Create header
        int columnIndex = 0;
        foreach (ExcelColumn clm in columns)
        {
            ICell cellE = rowE.CreateCell(columnIndex, CellType.STRING);
            cellE.SetCellValue(clm.HeaderText);
            cellE.CellStyle = cellStyleHeader;
            if (clm.Width > 0)
            {
                worksheet.SetColumnWidth(columnIndex, Globals.Object_GetValue<Int32>(clm.Width * 45));
            }
            else
            {
                worksheet.AutoSizeColumn(columnIndex);
            }
            //
            if (!string.IsNullOrEmpty(clm.DataFormat) && !lstStyle.ContainsKey(clm.DataFormat))
            {
                ICellStyle cellStyle = workbook.CreateCellStyle();
                cellStyle.DataFormat = workbook.CreateDataFormat().GetFormat(clm.DataFormat);
                lstStyle.Add(clm.DataFormat, cellStyle);
            }
            //
            columnIndex += 1;
        }
        for (int rowIndex = 0; rowIndex <= data.Count - 1; rowIndex++)
        {
            rowE = worksheet.CreateRow(rowIndex + 1);
            for (columnIndex = 0; columnIndex <= columns.Count - 1; columnIndex++)
            {
                ExcelColumn clm = columns[columnIndex];
                ICell cellE = default(ICell);
                object cellValue = data[rowIndex][clm.FieldName];
                switch (data.Table.Columns[clm.FieldName].DataType.Name)
                {
                    case "DateTime":
                        cellE = rowE.CreateCell(columnIndex);
                        if (!object.ReferenceEquals(cellValue, DBNull.Value))
                        {
                            cellE.SetCellValue(Convert.ToDateTime(cellValue));
                        }
                        
                        break;
                    default:
                        if (data.Table.Columns[clm.FieldName].DataType.IsValueType)
                        {
                            cellE = rowE.CreateCell(columnIndex, CellType.NUMERIC);
                            if (!object.ReferenceEquals(cellValue, DBNull.Value))
                            {
                                cellE.SetCellValue(Globals.Object_GetValue<double>(cellValue));
                            }
                        }
                        else
                        {
                            cellE = rowE.CreateCell(columnIndex);
                            if (!object.ReferenceEquals(cellValue, DBNull.Value))
                            {
                                cellE.SetCellValue(cellValue.ToString());
                            }
                        }
                        break;
                }
                if (lstStyle.ContainsKey(clm.DataFormat))
                    cellE.CellStyle = lstStyle[clm.DataFormat];
            }
        }
        //
        FileStream file = new FileStream(fileName, FileMode.Create);
        workbook.Write(file);
        file.Close();
        return fileName;
    }
    // Export Excel 
    public static string Excel_Export(DataGridView dgv)
    {
        string fileName = FormGlobals.App_TempFile("xls");
        Excel_Export(dgv, fileName);
        return fileName;
    }

    public static void Excel_Export(DataGridView dgv, string fileName)
    {
        Excel_Export(dgv, fileName, false);
    }

    public static ICell Excel_CellGet(ISheet sheet, Int32 row, string colName)
    {
        Int32 col = 0;
        col = Convert.ToInt32(Math.Floor((decimal)(colName.Substring(0, 1).ToCharArray())[0]));
        col = col - 65;
        return sheet.GetRow(row).GetCell(col);
    }

    public static string Excel_CellGetValue(ISheet sheet, string cellName)
    {
        Int32 row = 0;
        Int32 col = 0;
        Excel_CellNameParse(cellName, ref row, ref col);
        return Excel_CellGetValue(sheet, row, col);
    }

    private static void Excel_CellNameParse(string cellName, ref Int32 row, ref Int32 col)
    {
        cellName = cellName.ToUpper();
        row = Int32.Parse(cellName.Substring(1)) - 1;
        col = Convert.ToInt32( Math.Floor(   (decimal)(cellName.Substring(0, 1).ToCharArray())[0]  ) );
        col = col - 65;
    }

    private static string Excel_ColumnName(Int32 colIndex)
    {
        return (colIndex + 65).ToString();
    }

    public static string Excel_CellGetValue(ISheet sheet, Int32 row, Int32 col)
    {
        return Excel_CellGetValue(sheet.GetRow(row), col);
    }

    public static string Excel_CellGetValue(IRow row, Int32 col)
    {
        if ((row != null))
        {
            return Excel_CellGetValue(row.GetCell(col));
        }
        else
        {
            return null;
        }
    }

    public static string Excel_CellGetValue(ICell cell)
    {
        string retVal = Globals.Object_GetValue<string>( Excel_CellValue(cell) );
        return retVal;
    }

    public static object Excel_CellValue(ICell cell)
    {
        if (cell == null)
            return null;
        //
        object retVal = null;
        switch (cell.CellType)
        {
            case CellType.NUMERIC:
                try
                {
                    if (cell.NumericCellValue.ToString() != cell.ToString())
                    {
                        retVal = cell.DateCellValue;
                    }
                    else
                    {
                        retVal = cell.NumericCellValue;
                    }
                }
                catch (Exception ex)
                {
                    retVal = cell.NumericCellValue;
                }
                break;
            case CellType.STRING:
                retVal = cell.StringCellValue;
                break;
            case CellType.BOOLEAN:
                retVal = cell.BooleanCellValue;
                break;
            case CellType.FORMULA:
                FormulaEvaluator evaluator = cell.Sheet.Workbook.GetCreationHelper().CreateFormulaEvaluator();
                CellValue cellValue = null;
                cellValue = evaluator.Evaluate(cell);
                if ((cellValue != null))
                {
                    switch (cellValue.CellType)
                    {
                        case CellType.NUMERIC:
                            retVal = cellValue.NumberValue;
                            break;
                        case CellType.STRING:
                            retVal = cellValue.StringValue;
                            break;
                        default:
                            break;
                        //retVal= "";
                    }
                }
                break;
            default:
                break;
            //retVal = "";
        }
        return retVal;
    }

    public static void Excel_CellSetStyle(ISheet sheet, Int32 row, Int32 col, ICellStyle style)
    {
        IRow rowE = sheet.GetRow(row);
        if ((rowE != null))
        {
            Excel_CellSetStyle(rowE, col, style);
        }
        else
        {
            Excel_CellSetStyle(sheet.CreateRow(row), col, style);
        }
    }

    public static void Excel_CellSetStyle(IRow row, Int32 col, ICellStyle style)
    {
        if (col < row.LastCellNum)
        {
            Excel_CellSetStyle(row.GetCell(col), style);
        }
        else
        {
            Excel_CellSetStyle(row.CreateCell(col), style);
        }
    }

    public static void Excel_CellSetStyle(ICell cell, ICellStyle style)
    {
        cell.CellStyle = style;
    }

    public static ICell Excel_GetCell(ISheet sheet, string cellName)
    {
        Int32 row = 0;
        Int32 col = 0;
        Excel_CellNameParse(cellName, ref row, ref col);
        return sheet.GetRow(row).GetCell(col);
    }

    public static void Excel_CellSetValue(ISheet sheet, string cellName, object value)
    {
        Excel_CellSetValue(sheet, cellName, value, "");
    }

    public static void Excel_CellSetValue(ISheet sheet, string cellName, object value, string format)
    {
        ICell cell = Excel_GetCell(sheet, cellName);
        Excel_CellSetValue(cell, value, format);
    }

    public static void Excel_CellSetValue(ISheet sheet, Int32 row, Int32 col, object value)
    {
        Excel_CellSetValue(sheet, row, col, value, "");
    }

    public static void Excel_CellSetValue(ISheet sheet, Int32 row, Int32 col, object value, string format)
    {
        IRow rowE = sheet.GetRow(row);
        if ((rowE != null))
        {
            Excel_CellSetValue(rowE, col, value, format);
        }
        else
        {
            Excel_CellSetValue(sheet.CreateRow(row), col, value, format);
        }
    }

    public static void Excel_CellSetValue(IRow row, Int32 col, object value, string format)
    {
        if (col < row.LastCellNum)
        {
            Excel_CellSetValue(row.GetCell(col), value, format);
        }
        else
        {
            Excel_CellSetValue(row.CreateCell(col), value, format);
        }
    }

    public static void Excel_CellSetValue(ICell cell, object value, string format)
    {
        Type t = value.GetType();
        switch (t.Name)
        {
            case "DateTime":
                cell.SetCellValue(Convert.ToDateTime(value));
                break;
            default:
                if (t.IsValueType)
                {
                    cell.SetCellType(CellType.NUMERIC);
                    cell.SetCellValue(Globals.Object_GetValue<double>(value));
                }
                else
                {
                    cell.SetCellType(CellType.STRING);
                    cell.SetCellValue(value.ToString());
                }
                break;
        }
        if ((!string.IsNullOrEmpty(format)))
        {
            ICellStyle cellStyle = cell.Sheet.Workbook.CreateCellStyle();
            cellStyle.CloneStyleFrom(cell.CellStyle);
            cellStyle.DataFormat = cell.Sheet.Workbook.CreateDataFormat().GetFormat(format);
            cell.CellStyle = cellStyle;
        }
    }

    public static void Excel_Export(DataGridView dgv, string fileName, bool autoSize)
    {
        HSSFWorkbook workbook = new HSSFWorkbook();
        ISheet worksheet = workbook.CreateSheet("Sheet1");
        Excel_PageSetup(worksheet);
        //Auto size
        List<string> colText = new List<string>();
        IRow rowE = worksheet.CreateRow(0);
        //Define CellStyle for header
        ICellStyle cellStyleHeader = workbook.CreateCellStyle();
        IFont f = workbook.CreateFont();
        f.Boldweight = 700;
        cellStyleHeader.SetFont(f);
        //Create header
        int colHeaderIdx = 0;
        foreach (DataGridViewColumn clm in dgv.Columns)
        {
            if (clm.Visible)
            {
                ICell cellE = rowE.CreateCell(colHeaderIdx, CellType.STRING);
                cellE.SetCellValue(clm.HeaderText);
                cellE.CellStyle = cellStyleHeader;
                if ((!autoSize))
                {
                    worksheet.SetColumnWidth(Globals.Object_GetValue<Int32>(colHeaderIdx), Globals.Object_GetValue<Int32>(clm.Width * 45));
                }
                else
                {
                    colText.Add(clm.HeaderText);
                }
                colHeaderIdx++;
            }

        }

        //Define CellStyle format Date
        ICellStyle cellStyleDate = workbook.CreateCellStyle();
        cellStyleDate.DataFormat = workbook.CreateDataFormat().GetFormat(Globals.CS_DISPLAY_DATE_FORMAT);

        //Define CellStyle format Decimal
        ICellStyle cellStyleDec = workbook.CreateCellStyle();
        cellStyleDec.DataFormat = workbook.CreateDataFormat().GetFormat(Globals.CS_DISPLAY_NUMBER_FORMAT);

        foreach (DataGridViewRow row in dgv.Rows)
        {
            if ((!row.IsNewRow))
            {
                rowE = worksheet.CreateRow(row.Index + 1);

                int colIdx = 0;
                foreach (DataGridViewCell cell in row.Cells)
                {
                    if (cell.OwningColumn.Visible)
                    {
                        ICell cellE = default(ICell);

                        string displayValue = null;
                        switch (cell.ValueType.Name)
                        {
                            case "DateTime":
                                cellE = rowE.CreateCell(colIdx);
                                cellE.SetCellValue(Convert.ToDateTime(cell.Value));
                                cellE.CellStyle = cellStyleDate;
                                //
                                displayValue = (Convert.ToDateTime(cell.Value)).ToString(Globals.CS_DISPLAY_DATE_FORMAT);
                                break;
                            default:
                                if (cell.ValueType.IsValueType)
                                {
                                    cellE = rowE.CreateCell(colIdx, CellType.NUMERIC);
                                    cellE.CellStyle = cellStyleDec;
                                    //
                                    displayValue = Globals.Object_GetValue<string>(cell.Value);
                                    if (!string.IsNullOrEmpty(displayValue))
                                    {
                                        cellE.SetCellValue( Globals.DB_GetValue<double>(cell.Value,0));
                                        if (displayValue.EndsWith(".00"))
                                            displayValue = displayValue.Substring(0, displayValue.Length - 3);
                                    }
                                    else
                                    {
                                        cellE.SetCellValue("");
                                    }
                                }
                                else
                                {
                                    if (object.ReferenceEquals(cell.Value, DBNull.Value))
                                    {
                                        cellE = rowE.CreateCell(colIdx);
                                    }
                                    else
                                    {
                                        rowE.CreateCell(colIdx).SetCellValue(Globals.Object_GetValue<string>(cell.Value));
                                    }
                                    displayValue = Globals.Object_GetValue<string>(cell.Value);
                                }

                                break;
                        }
                        //auto size
                        if (autoSize)
                        {
                            //If colText(cell.OwningColumn.DisplayIndex).Length < displayValue.Length Then
                            // colText(cell.OwningColumn.DisplayIndex) = displayValue
                            //End If
                        }
                        //
                        colIdx++;
                    }
                }
            }
        }
        //
        if (autoSize)
        {
            for (Int32 i = 0; i <= colText.Count - 1; i++)
            {
                //Dim w As Single = MeasureTextWidth(colText(i), New System.Drawing.Font("Arial", 10.0F))
                //worksheet.SetColumnWidth(i, UISetting.ToInt32(w * 35.5))
                worksheet.AutoSizeColumn(i);
            }
        }
        //
        FileStream file = new FileStream(fileName, FileMode.Create);
        workbook.Write(file);
        file.Close();
    }

    private static void Excel_PageSetup(ISheet worksheet)
    {
        Excel_PageSetup(worksheet, PaperSize.B4, false);
    }

    private static void Excel_PageSetup(ISheet worksheet, PaperSize iPaperSize)
    {
        Excel_PageSetup(worksheet, iPaperSize, false);
    }

    private static void Excel_PageSetup(ISheet worksheet, PaperSize iPaperSize, bool bLandScape)
    {
        IPrintSetup prsetup = worksheet.PrintSetup;
        prsetup.FitHeight = 0;
        prsetup.FitWidth = 1;
        prsetup.Scale = 1;
        prsetup.PaperSize = (short)iPaperSize;
        prsetup.Landscape = bLandScape;
        worksheet.Autobreaks = true;
        worksheet.FitToPage = true;
    }
    // --------- 
    public static string Excel_ExportWithTemplate(DataTable data, string template_file, Dictionary<string, object> parameters,String sSavedPath ="")
    {
        //ByVal columns As List(Of ExcelColumn), ByVal formats As List(Of String),
        //,ByVal x As Integer, ByVal y As Integer
        int totalrows = 0;
        if ((data != null))
        {
            totalrows = data.Rows.Count;
        }
        int x = 0;
        int y = 0;
        List<string> columns = new List<string>();
        //Dim formats As List(Of String)
        List<ICellStyle> styles = new List<ICellStyle>();

        Stream stream = null;
        HSSFWorkbook workbook = default(HSSFWorkbook);
        HSSFSheet sheet = default(HSSFSheet);
        if (!(template_file == null | string.IsNullOrEmpty(template_file)))
        {
            stream = File.Open(template_file, FileMode.Open);
            workbook = new HSSFWorkbook(stream);
            sheet = (HSSFSheet) workbook.GetSheetAt(0);
        }
        else
        {
            workbook = new HSSFWorkbook();
            sheet = (HSSFSheet)workbook.CreateSheet();
        }
        sheet.SetActive(true);
        //Detect data row, columns, format
        Regex regex = new Regex("^{#([\\w]+)(([|](.+))|)}$", RegexOptions.Singleline);
        int min = 0;
        int height = 0;
        min = int.MaxValue;
        bool started = false;
        for (int i = 0; i <= sheet.LastRowNum - 1; i++)
        {
            IRow irow = sheet.GetRow(i);
            if (irow == null)
            {
                continue;
            }
            for (int j = 0; j <= irow.LastCellNum - 1; j++)
            {
                ICell icell = irow.GetCell(j);
                string format = null;
                string column = null;
                ICellStyle style = icell.CellStyle;
                if (icell.CellType == CellType.STRING)
                {
                    if (!string.IsNullOrEmpty(icell.StringCellValue))
                    {
                        if (regex.IsMatch(icell.StringCellValue))
                        {
                            if (started == false)
                            {
                                started = true;
                                j = -1;
                                continue;
                            }
                            min = Math.Min(min, j);
                            Match m = regex.Match(icell.StringCellValue);
                            column = m.Groups[1].Value;
                            if (m.Groups.Count >= 5)
                            {
                                if (!string.IsNullOrEmpty(m.Groups[4].Value))
                                {
                                    format = m.Groups[4].Value;
                                }
                            }
                        }
                    }
                }
                if (started)
                {
                    columns.Add(column);
                    //formats.Add(format)
                    if (!string.IsNullOrEmpty(format))
                    {
                        style.DataFormat = workbook.CreateDataFormat().GetFormat(format);
                    }
                    styles.Add(style);
                }
            }
            if (started)
            {
                height = irow.Height;
                y = i;
                break; // TODO: might not be correct. Was : Exit For
            }
        }

        //Replace all Parameter
        if ((parameters != null))
        {
            regex = new Regex("{[$]([\\w]+)(([|](.+?))|)}");
            for (int i = 0; i <= sheet.LastRowNum - 1; i++)
            {
                IRow irow = sheet.GetRow(i);
                if (irow == null)
                {
                    continue;
                }
                for (int j = 0; j <= irow.LastCellNum - 1; j++)
                {
                    ICell icell = irow.GetCell(j);
                    if (icell == null)
                    {
                        continue;
                    }
                    if (icell.CellType == CellType.STRING)
                    {
                        if (!string.IsNullOrEmpty(icell.StringCellValue))
                        {
                            string cellvalue = icell.StringCellValue;
                            if (regex.IsMatch(icell.StringCellValue))
                            {
                                MatchCollection matchs = regex.Matches(icell.StringCellValue);
                                foreach (Match match in matchs)
                                {
                                    string column = match.Groups[1].Value;
                                    string format = string.Empty;
                                    string replacewith = string.Empty;
                                    if (match.Groups.Count >= 5)
                                    {
                                        if (!string.IsNullOrEmpty(match.Groups[4].Value))
                                        {
                                            format = match.Groups[4].Value;
                                        }
                                    }
                                    if (parameters.ContainsKey(column))
                                    {
                                        object value = parameters[column];
                                        if (Globals.IsDBNull(value) | value == null)
                                        {
                                            replacewith = string.Empty;
                                        }
                                        else
                                        {
                                            if (!string.IsNullOrEmpty(format))
                                            {
                                                replacewith = string.Format("{0:" + format + "}", value);
                                            }
                                            else
                                            {
                                                replacewith = value.ToString();
                                            }
                                        }
                                        //Select Case value.GetType()
                                        // Case "DateTime"
                                        // replacewith = Globals.Object_GetValue(Of DateTime)(value).ToString(format)
                                        // Case "Decimal"
                                        // replacewith = Globals.Object_GetValue(Of Decimal)(value).ToString(format)
                                        // Case "Decimal"
                                        // replacewith = Globals.Object_GetValue(Of Decimal)(value).ToString(format)
                                        //End Select
                                        cellvalue = cellvalue.Replace(match.Value, replacewith);
                                    }
                                }
                            }
                            icell.SetCellValue(cellvalue);
                        }
                    }
                }
            }
        }

        //Copy from insert position to lastest cell

        int last = sheet.LastRowNum;
        int start = y + 1;
        if (totalrows == 0)
        {
            sheet.RemoveRow(sheet.GetRow(y));
        }

        sheet.ShiftRows(start, last, totalrows - 1);

        if (totalrows > 0)
        {
            for (int i = last; i >= start; i += -1)
            {
                IRow nrow = sheet.GetRow(i + totalrows - 1);
                IRow orow = sheet.GetRow(i);
                if ((orow != null) & (nrow != null))
                {
                    nrow.Height = orow.Height;
                    nrow.ZeroHeight = orow.ZeroHeight;
                }

            }
        }
        else
        {
            for (int i = start; i <= last; i += 1)
            {
                IRow nrow = sheet.GetRow(i + totalrows - 1);
                IRow orow = sheet.GetRow(i);
                if ((orow != null) & (nrow != null))
                {
                    nrow.Height = orow.Height;
                    nrow.ZeroHeight = orow.ZeroHeight;
                }
            }
        }


        if ((data != null))
        {
            IRow row = default(IRow);
            for (int i = 0; i <= totalrows - 1; i++)
            {
                row = sheet.CreateRow(y + i);
                row.Height = (short)height;
                for (int j = 0; j <= columns.Count - 1; j++)
                {
                    int column_index = x + j;
                    ICell cell = row.CreateCell(column_index);
                    cell.CellStyle = styles[j];
                    if (!string.IsNullOrEmpty(columns[j]))
                    {
                        if (!data.Columns.Contains(columns[j]))
                        {
                            continue;
                        }
                    }
                    else
                    {
                        continue;
                    }
                    object cell_value = data.Rows[i][columns[j]];
                    if ((cell_value != null) & !Globals.IsDBNull(cell_value))
                    {
                        switch (cell_value.GetType().Name)
                        {
                            case "DateTime":
                                cell.SetCellValue(Globals.Object_GetValue<DateTime>(cell_value));
                                break;
                            default:
                                if (data.Columns[columns[j]].DataType.IsValueType)
                                {
                                    cell.SetCellType(CellType.NUMERIC);
                                    cell.SetCellValue(Globals.Object_GetValue<double>(cell_value));
                                }
                                else
                                {
                                    cell.SetCellType(CellType.STRING);
                                    cell.SetCellValue(cell_value.ToString());
                                }
                                break;
                        }
                    }
                }
            }
        }

        string file_name = String.IsNullOrEmpty(sSavedPath) ? FormGlobals.App_TempFile("xls") : sSavedPath;
        sheet.ForceFormulaRecalculation = true;
        try
        {
            HSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook);

        }
        catch (Exception ex)
        {
        }
        Stream out_stream = File.Open(file_name, FileMode.Create);
        workbook.Write(out_stream);
        out_stream.Close();
        if ((stream != null))
        {
            stream.Close();
        }

        return file_name;
    }

    public static string Excel_ExportWithTemplate(DataSet data, string template_file, Dictionary<string, object> parameters, String sSavedPath = "")
    {
        return Excel_ExportWithTemplate(data, template_file, parameters, new int[]{},sSavedPath);
    }

    public static string Excel_ExportWithTemplate(DataSet data, string template_file, Dictionary<string, object> parameters, int[] ignoreSheet, String sSavedPath = "")
    {
        Stream stream = null;
        HSSFWorkbook workbook = default(HSSFWorkbook);
        HSSFSheet sheet = default(HSSFSheet);
        if (!(template_file == null | string.IsNullOrEmpty(template_file)))
        {
            stream = File.Open(template_file, FileMode.Open);
            workbook = new HSSFWorkbook(stream);
            sheet = (HSSFSheet)workbook.GetSheetAt(0);
        }
        else
        {
            workbook = new HSSFWorkbook();
            sheet = (HSSFSheet)workbook.CreateSheet();
        }
        //Replace all Parameter
        Regex regex = default(Regex);
        if ((parameters != null))
        {
            regex = new Regex("{[$]([\\w]+)(([|](.+?))|)}");
            for (int s = 0; s <= workbook.NumberOfSheets - 1; s++)
            {
                if (ignoreSheet==null || ignoreSheet.Contains(s))
                    continue;
                //
                sheet = (HSSFSheet)workbook.GetSheetAt(s);
                sheet.SetActive(true);
                for (int i = 0; i <= sheet.LastRowNum - 1; i++)
                {
                    IRow irow = sheet.GetRow(i);
                    if (irow == null)
                    {
                        continue;
                    }
                    for (int j = 0; j <= irow.LastCellNum - 1; j++)
                    {
                        ICell icell = irow.GetCell(j);
                        if (icell == null)
                        {
                            continue;
                        }
                        if (icell.CellType == CellType.STRING)
                        {
                            if (!string.IsNullOrEmpty(icell.StringCellValue))
                            {
                                string cellvalue = icell.StringCellValue;
                                if (regex.IsMatch(icell.StringCellValue))
                                {
                                    MatchCollection matchs = regex.Matches(icell.StringCellValue);
                                    foreach (Match match in matchs)
                                    {
                                        string column = match.Groups[1].Value;
                                        string format = string.Empty;
                                        string replacewith = string.Empty;
                                        if (match.Groups.Count >= 5)
                                        {
                                            if (!string.IsNullOrEmpty(match.Groups[4].Value))
                                            {
                                                format = match.Groups[4].Value;
                                            }
                                        }
                                        if (parameters.ContainsKey(column))
                                        {
                                            object value = parameters[column];
                                            if (Globals.IsDBNull(value) | value == null)
                                            {
                                                replacewith = string.Empty;
                                            }
                                            else
                                            {
                                                if (!string.IsNullOrEmpty(format))
                                                {
                                                    replacewith = string.Format("{0:" + format + "}", value);
                                                }
                                                else
                                                {
                                                    replacewith = value.ToString();
                                                }
                                            }
                                            cellvalue = cellvalue.Replace(match.Value, replacewith);
                                        }
                                    }
                                }
                                icell.SetCellValue(cellvalue);
                            }
                        }
                    }
                }
            }
        }
        for (int s = 0; s <= workbook.NumberOfSheets - 1; s++)
        {
            if (ignoreSheet ==null || ignoreSheet.Contains(s))
                continue;
            //
            sheet = (HSSFSheet)workbook.GetSheetAt(s);
            sheet.SetActive(true);
            for (int t = 0; t <= data.Tables.Count - 1; t++)
            {
                DataTable table = data.Tables[t];
                int totalrows = 0;
                if ((data != null))
                {
                    totalrows = table.Rows.Count;
                }
                int x = 0;
                int y = 0;
                x = 0;
                y = 0;
                List<string> columns = new List<string>();
                List<ICellStyle> styles = new List<ICellStyle>();

                //Detect data row, columns, format
                regex = new Regex("^{#" + table.TableName + "[\\.]([\\w]+)(([|](.+))|)}$", RegexOptions.Singleline);
                int min = 0;
                int height = 0;
                min = int.MaxValue;
                bool started = false;
                for (int i = 0; i <= sheet.LastRowNum - 1; i++)
                {
                    IRow irow = sheet.GetRow(i);
                    if (irow == null)
                    {
                        continue;
                    }
                    for (int j = 0; j <= irow.LastCellNum - 1; j++)
                    {
                        ICell icell = irow.GetCell(j);
                        string format = null;
                        string column = null;
                        ICellStyle style = null;
                        bool ismerge = false;
                        if ((icell != null))
                        {
                            style = icell.CellStyle;
                            ismerge = icell.IsMergedCell;
                            if (icell.CellType == CellType.STRING)
                            {
                                if (icell.StringCellValue.StartsWith("F1"))
                                {
                                    int a = 1;
                                }
                                if (!string.IsNullOrEmpty(icell.StringCellValue))
                                {
                                    if (regex.IsMatch(icell.StringCellValue))
                                    {
                                        if (started == false)
                                        {
                                            started = true;
                                            j = -1;
                                            continue;
                                        }
                                        min = Math.Min(min, j);
                                        Match m = regex.Match(icell.StringCellValue);
                                        column = m.Groups[1].Value;
                                        if (m.Groups.Count >= 5)
                                        {
                                            if (!string.IsNullOrEmpty(m.Groups[4].Value))
                                            {
                                                format = m.Groups[4].Value;
                                            }
                                        }
                                    }
                                }
                            }
                        }
                        if (started)
                        {
                            columns.Add(column);
                            //formats.Add(format)
                            if (!string.IsNullOrEmpty(format))
                            {
                                style.DataFormat = workbook.CreateDataFormat().GetFormat(format);
                            }
                            styles.Add(style);
                        }
                    }
                    if (started)
                    {
                        height = irow.Height;
                        y = i;
                        break; // TODO: might not be correct. Was : Exit For
                    }
                }

                if (started)
                {
                    //Copy from insert position to lastest cell
                    int last = sheet.LastRowNum;
                    int start = y + 1;
                    if (totalrows == 0)
                    {
                        sheet.RemoveRow(sheet.GetRow(y));
                    }
                    if ((totalrows != 1))
                    {
                        sheet.ShiftRows(start, last, totalrows - 1);
                    }

                    if (totalrows > 0)
                    {
                        for (int i = last; i >= start; i += -1)
                        {
                            IRow nrow = sheet.GetRow(i + totalrows - 1);
                            IRow orow = sheet.GetRow(i);
                            if ((orow != null))
                            {
                                nrow.Height = orow.Height;
                                nrow.ZeroHeight = orow.ZeroHeight;
                            }

                        }
                    }
                    else
                    {
                        for (int i = start; i <= last; i += 1)
                        {
                            IRow nrow = sheet.GetRow(i + totalrows - 1);
                            IRow orow = sheet.GetRow(i);
                            nrow.Height = orow.Height;
                            nrow.ZeroHeight = orow.ZeroHeight;
                        }
                    }

                    IRow row = default(IRow);
                    for (int i = 0; i <= totalrows - 1; i++)
                    {
                        row = sheet.CreateRow(y + i);
                        row.Height = (short)height;
                        for (int j = 0; j <= columns.Count - 1; j++)
                        {
                            int column_index = x + j;
                            ICell cell = row.CreateCell(column_index);
                            if (styles[j] == null)
                            {
                                cell.CellStyle = workbook.CreateCellStyle();
                            }
                            else
                            {
                                cell.CellStyle = styles[j];
                            }

                            if (!string.IsNullOrEmpty(columns[j]))
                            {
                                if (!table.Columns.Contains(columns[j]))
                                {
                                    continue;
                                }
                            }
                            else
                            {
                                continue;
                            }

                            //--Gán value vào từng cell tương ứng

                            object cell_value = table.Rows[i][columns[j]];
                            if ((cell_value != null) & !Globals.IsDBNull(cell_value))
                            {
                                switch (cell_value.GetType().Name)
                                {
                                    case "DateTime":
                                        cell.SetCellValue(Globals.Object_GetValue<DateTime>(cell_value));
                                        break;
                                    default:
                                        if (table.Columns[columns[j]].DataType.IsValueType)
                                        {
                                            cell.SetCellType(CellType.NUMERIC);
                                            //cell.SetCellValue(Globals.Object_GetValue<string>(cell_value));
                                            short cellFormat = cell.CellStyle.DataFormat;
                                            
                                            if (cell_value.IsNumber())
                                            {
                                                cell.SetCellValue(Convert.ToDouble(cell_value));
                                            }
                                            else
                                                cell.SetCellValue(Globals.Object_GetValue<string>(cell_value));
                                            
                                            //Nếu  = 0 thì để trống
                                            if ( (cell_value.IsNumber() && Convert.ToDouble(cell_value) == 0) || cell_value.ToString() == "0")
                                                cell.SetCellValue("");
                                        }
                                        else
                                        {
                                            cell.SetCellType(CellType.STRING);
                                            cell.SetCellValue(cell_value.ToString());
                                        }
                                        break;
                                }
                            }
                        }
                    }
                }
            }
        }
        //Delete ignore sheet
        List<int> lstIgnore = new List<int>();
        lstIgnore.AddRange(ignoreSheet);
        lstIgnore.Sort();
        for (int i = lstIgnore.Count - 1; i >= 0; i += -1)
        {
            workbook.RemoveSheetAt(lstIgnore[i]);
        }
        //
        string file_name = String.IsNullOrEmpty(sSavedPath) ? FormGlobals.App_TempFile("xls") : sSavedPath;
        sheet.ForceFormulaRecalculation = true;
        try
        {
            HSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook);
        }
        catch (Exception ex)
        {
        }
        Stream out_stream = File.Open(file_name, FileMode.Create);
        workbook.Write(out_stream);
        out_stream.Close();
        if ((stream != null))
        {
            stream.Close();
        }

        return file_name;
    }

    public static DataTable Excel_GetData(string fileName, bool firstHeader, int startTableIdx = 0, bool ignoreEmptyRow = false)
    {
        DataTable retVal = null;
        //
        FileStream file = null;
        try
        {
            file = new FileStream(fileName, FileMode.Open, FileAccess.Read);
            HSSFWorkbook book = new HSSFWorkbook(file);
            //
            if (book.NumberOfSheets > 0)
            {
                ISheet sheet = book.GetSheetAt(0);
                if (sheet.LastRowNum < 0)
                    return null;
                int rowFirst = sheet.FirstRowNum + startTableIdx;
                IRow row = default(IRow);
                retVal = new DataTable();
                //Create table
                if (firstHeader)
                {
                    row = sheet.GetRow(rowFirst);
                    for (int i = 0; i <= row.Cells.Count - 1; i++)
                    {
                        string columnName = Excel_CellGetValue(row, i);
                        retVal.Columns.Add(new DataColumn(columnName));
                    }
                    rowFirst += 1;
                }
                else
                {
                    for (int i = 0; i <= ((int)('Z') - (int)('A')) - 1; i++)
                    {
                        string columnName = Excel_ColumnName(i);
                        retVal.Columns.Add(new DataColumn(columnName));
                    }
                }
                //
                for (int i = rowFirst; i <= sheet.LastRowNum; i++)
                {
                    row = sheet.GetRow(i);
                    //if (row == null && ignoreEmptyRow)
                    //    continue;
                    //
                    DataRow dr = retVal.NewRow();
                    if (row != null)
                    {
                        for (int j = 0; j <= row.Cells.Count - 1; j++)
                        {
                            dr[j] = Excel_CellGetValue(row, j);
                        }
                    }
                    retVal.Rows.Add(dr);
                }
                retVal.AcceptChanges();
            }
            book.Dispose();
        }
        finally
        {
            if ((file != null))
            {
                file.Close();
            }
        }
        return retVal;
    }
}
